CREATE TABLE jms_dws.dws_center_scan_lack_agg (
                                                     dt                        date COMMENT '日期',
                                                     scan_code         varchar(80) comment '操作中心编码',
                                                     order_source_name varchar(300) comment '订单来源名称',
                                                     agent_code        varchar(80) comment '操作中心代理区编码',
                                                     mage_region_code  varchar(80) comment '管理大区编码',
                                                     scan_name         varchar(300) comment '操作中心名称',
                                                     agent_name        varchar(300) comment '操作中心代理区名称',
                                                     mage_region_name  varchar(300) comment '管理大区名称',
                                                     order_source_code varchar(80) comment '订单来源编码',
                                                     ought_scan_cnt    bigint comment '应扫描运单数量',
                                                     arrival_lack_cnt  bigint comment '卸车到件漏扫数量',
                                                     arrival_lack_rate double comment '卸车到件漏扫率',
                                                     send_lack_cnt     bigint comment '装车发件漏扫数量',
                                                     send_lack_rate    double comment '装车发件漏扫率',
                                                     com_lack_rate     double comment '综合漏扫率'
) ENGINE=OLAP
    DUPLICATE KEY(`dt`, `scan_code`,`order_source_name`,`agent_code`,`mage_region_code`)
    COMMENT '转运中心漏扫汇总'
    PARTITION BY RANGE(`dt`)
    (
    PARTITION  p20220212 VALUES [("2022-02-12"), ("2022-02-13")),
    PARTITION  p20220213 VALUES [("2022-02-13"), ("2022-02-14")),
    PARTITION  p20220214 VALUES [("2022-02-14"), ("2022-02-15")),
    PARTITION  p20220215 VALUES [("2022-02-15"), ("2022-02-16")),
    PARTITION  p20220216 VALUES [("2022-02-16"), ("2022-02-17")),
    PARTITION  p20220217 VALUES [("2022-02-17"), ("2022-02-18")),
    PARTITION  p20220218 VALUES [("2022-02-18"), ("2022-02-19")),
    PARTITION  p20220219 VALUES [("2022-02-19"), ("2022-02-20")),
    PARTITION  p20220220 VALUES [("2022-02-20"), ("2022-02-21")),
    PARTITION  p20220221 VALUES [("2022-02-21"), ("2022-02-22")),
    PARTITION  p20220222 VALUES [("2022-02-22"), ("2022-02-23")),
    PARTITION  p20220223 VALUES [("2022-02-23"), ("2022-02-24")),
    PARTITION  p20220224 VALUES [("2022-02-24"), ("2022-02-25")),
    PARTITION  p20220225 VALUES [("2022-02-25"), ("2022-02-26")),
    PARTITION  p20220226 VALUES [("2022-02-26"), ("2022-02-27")),
    PARTITION  p20220227 VALUES [("2022-02-27"), ("2022-02-28")),
    PARTITION  p20220228 VALUES [("2022-02-28"), ("2022-03-01")),
    PARTITION  p20220301 VALUES [("2022-03-01"), ("2022-03-02")),
    PARTITION  p20220302 VALUES [("2022-03-02"), ("2022-03-03")),
    PARTITION  p20220303 VALUES [("2022-03-03"), ("2022-03-04")),
    PARTITION  p20220304 VALUES [("2022-03-04"), ("2022-03-05")),
    PARTITION  p20220305 VALUES [("2022-03-05"), ("2022-03-06"))
    )
    DISTRIBUTED BY HASH(`scan_code`) BUCKETS 10
    PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.start" = "-180",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "10",
    "in_memory" = "false",
    "storage_format" = "V2"
    );
